

'''
-- 创建 token 表
CREATE TABLE tokens (
    id SERIAL PRIMARY KEY,
    user_id INT,
    token VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- token 有效期 12 小时
    expires_at TIMESTAMP DEFAULT NOW() + INTERVAL '12 hours',
    -- 添加外键约束，关联 users 表
    FOREIGN KEY (user_id) REFERENCES users(user_id),
);

SELECT SETVAL('tokens_id_seq',max(id),TRUE) FROM tokens;
'''

import uuid
import psycopg2
import datetime

from . import logger

class TokensTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor


    # 生成 token
    @staticmethod
    def generate_token():
        
        return str(uuid.uuid4())
    


    # 添加 token
    def add_token(self, user_id):

        token = self.generate_token()

        try:
            self.cursor.execute("INSERT INTO tokens (user_id, token) VALUES (%s, %s)", (user_id, token))
            logger.info(f"Token {token} added successfully for user {user_id}.")
            return True, token
        except psycopg2.errors.UniqueViolation as e: # 一旦出现唯一键违反，说明 token 已经存在，返回 None, 但一般情况下不会出现
            logger.warning(f"Token {token} already exists for user {user_id}.")
            return False, str(e)

        except Exception as e:
            logger.error(f"Error adding token {token} for user {user_id}: {e}")
            return False, str(e)
        

    # 获取 token
    def get_token_by_user_id(self, user_id):
    
        try:
            self.cursor.execute("SELECT * FROM tokens WHERE user_id = %s order by created_at desc limit 1", (user_id,))
            token = self.cursor.fetchone()

            if token is None:
                logger.warning(f"No token found for user {user_id}.")
                return False, None
            else:
                token = token["token"]
                expires_at = token["expires_at"]
                if expires_at < datetime.datetime.now():
                    logger.warning(f"Token {token} for user {user_id} has expired.")
                    return False, None
                

        except Exception as e:        
            logger.error(f"Error getting token for user {user_id}: {e}")
            return False, None
        
        else:
            logger.info(f"Got token {token} for user {user_id} successfully.")
            return True, token


    # 验证 token
    def verify_token(self, token):
    
        try:
            self.cursor.execute("SELECT * FROM tokens WHERE token = %s", (token,))
            token_info = self.cursor.fetchone()

            if token_info is None:
                logger.warning(f"Token {token} not found.")
                return False, None
            else:
                user_id = token_info["user_id"]
                expires_at = token_info["expires_at"]
                if expires_at < datetime.datetime.now():
                    logger.warning(f"Token {token} has expired.")
                    return False, None

        except Exception as e:        
            logger.error(f"Error verifying token {token}: {e}")
            return False, None
        
        else:
            logger.info(f"Token {token} verified successfully for user {user_id}.")
            return True, user_id


    # 刷新 token
    def refresh_token(self, token):
    
        try:
            self.cursor.execute("UPDATE tokens SET expires_at = NOW() + INTERVAL '12 hours' WHERE token = %s", (token,))
            self.connection.commit()
            logger.info(f"Token {token} refreshed successfully.")
            return True
        except Exception as e:        
            logger.error(f"Error refreshing token {token}: {e}")
            return False
    
    # 删除 token
    def delete_token(self, token):
        
        try:
            self.cursor.execute("DELETE FROM tokens WHERE token = %s", (token,))
            logger.info(f"Token {token} deleted successfully.")
            return True
        except Exception as e:        
            logger.error(f"Error deleting token {token}: {e}")
            return False
        
